package planning.metier;

import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.ServletException;

public class Planning implements I_Planning {
	private I_Bdd bdd;

	public Planning() throws SQLException, ServletException{
		this.bdd = new Bdd();
	}
	/* (non-Javadoc)
	 * @see Metier.I_Planning#addUS(java.lang.String, java.lang.String)
	 */
	@Override
	public String addUS(String nom, String etat, String desc, String pos) throws SQLException{
		String result = null;
		int rs = this.bdd.update(
				"INSERT INTO `userstory` ( `id` , `name` , `planning_state` , `planning_order` , `planning_andon` , `description` ) VALUES ( NULL , '"+nom+"' ,'"+etat+"' , "+pos+", 0,'"+desc+"')");
		if(rs==1){
			ResultSet resultSelect = bdd.select("SELECT `id` FROM `userstory` WHERE `name`='"+nom+"'");
			resultSelect.last();
			result = resultSelect.getObject(1).toString();
		}
		return result;
	}

	/* (non-Javadoc)
	 * @see Metier.I_Planning#andonUS(java.lang.String)
	 */
	@Override
	public String andonUS(String id) throws SQLException{
		String result="error";
		ResultSet resultSelect = this.bdd.select("SELECT `planning_andon` FROM `userstory` WHERE `id`='"+id+"'");
		if(resultSelect.last()){
			if(resultSelect.getBoolean(1)){
				this.bdd.update("UPDATE `userstory` SET `planning_andon`= 0 WHERE `id`='"+id+"'");
				result="off";
			}else{
				this.bdd.update("UPDATE `userstory` SET `planning_andon`= 1 WHERE `id`='"+id+"'");
				result="on";
			}
		}
		return result;
	}

	/* (non-Javadoc)
	 * @see Metier.I_Planning#deleteUS(java.lang.String)
	 */
	@Override
	public String deleteUS(String id) throws SQLException{
		String result = "delete error";
		int rs = this.bdd.update(
				"DELETE FROM `userstory` WHERE `id`='"+id+"'");
		if(rs==1){
			result="delete ok";
		}
		return result;
	}

	/* (non-Javadoc)
	 * @see Metier.I_Planning#detailUS(java.lang.String)
	 */
	@Override
	public String detailUS(String id) throws SQLException{
		String result = "error";
		ResultSet rs = this.bdd.select("SELECT * FROM `userstory` WHERE `id`='"+id+"'");
		while (rs.next ())
		{
			String etat = convertEtat(rs.getString(8));
			String desc = "<i>nc</i>";
			if(!rs.getString(4).isEmpty()){
				desc = rs.getString(4);
			}
			
			String imgAndon = "<img class=\"img-andon\" id=\"img-andon\" src=\"images/no_help.png\" />";
			if(rs.getBoolean(10)){
				imgAndon = "<img class=\"img-andon\" id=\"img-andon\" src=\"images/help.gif\" />";
			}
			result="<table align=\"left\">"+
					"<tr><td align=\"right\">ID:</td><td align=\"center\">"+rs.getString(1)+"</td></tr>"+
					"<tr><td align=\"right\">Titre:</td><td align=\"center\">"+rs.getString(2)+"</td></tr>"+
					"<tr><td align=\"right\">Description:</td><td align=\"center\">"+desc+"</td></tr>"+
					"<tr><td align=\"right\">Position:</td><td align=\"center\">"+rs.getString(9)+"</td></tr>"+
					"<tr><td align=\"right\">Etat:</td><td align=\"center\">"+etat+"</td></tr>"+
					"<tr><td align=\"right\">Andon:</td><td align=\"center\">"+imgAndon+"</td></tr>"+
					"</table>";
		}
		return result;
	}
	
	/* (non-Javadoc)
	 * @see Metier.I_Planning#dragNDropUS(java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	public String dragNDropUS(String columnold, String listold, String columnnew, String listnew) throws SQLException{
		String result = "error";
		boolean bresult1 = false;
		boolean bresult2 = false;
		String[] tabold = listold.split(",");
		String[] tabnew = listnew.split(",");	
		for (int i = 0; i < tabold.length; i++) {
			if(this.bdd.update("UPDATE `userstory` SET `planning_state`= "+columnold.charAt(1)+", `planning_order`="+i+" WHERE `id`='"+tabold[i]+"'")==1){
				bresult1 = true;
			}else{
				bresult1 = false;
			}
		}
		for (int i = 0; i < tabnew.length; i++) {
			if(this.bdd.update("UPDATE `userstory` SET `planning_state`= "+columnnew.charAt(1)+", `planning_order`="+i+" WHERE `id`='"+tabnew[i]+"'")==1){
				bresult2 = true;
			}else{
				bresult2 = false;
			}
		}
		if(bresult1 && bresult2) {
			result = "ok";
		}
		return result;
	}

	/* (non-Javadoc)
	 * @see Metier.I_Planning#editUS(java.lang.String)
	 */
	@Override
	public String editUS(String id) throws SQLException{
		String result = "error";
		ResultSet rs = this.bdd.select("SELECT * FROM `userstory` WHERE `id`='"+id+"'");
		while (rs.next ())
		{
			boolean andon = rs.getBoolean(10);
			String checkAndon = "";
			if(andon){
				checkAndon = "<input type=\"checkbox\" checked value=\"andon\"";//"<img id=\"img-andon\" src=\"images/help.gif\" />";
			}else{
				checkAndon = "<input type=\"checkbox\" id=\"andonUS\" value=\"andon\"";//"<img id=\"img-andon\" src=\"images/no_help.png\" />";
			}
			result="<table align=\"left\">"+
					"<tr><td align=\"right\">ID:</td><td align=\"center\">"+rs.getString(1)+"</td></tr>"+
					"<tr><td align=\"right\">Titre:</td><td align=\"center\"><input type=\"text\" id=\"titreUS\" class=\"ui-widget-content ui-corner-all\" size=\"32\" maxlength=\"32\" value=\""+rs.getString(2)+"\"></td></tr>"+
					"<tr><td align=\"right\">Description:</td><td align=\"center\"><textarea id=\"descUS\" class=\"ui-widget-content ui-corner-all\" rows=\"4\" cols=\"30\" maxlength=\"140\">"+rs.getString(4)+"</textarea></td></tr>"+
					"<tr><td align=\"right\">Andon:</td><td align=\"center\">"+checkAndon+"</td></tr>"+
					"</table>";
		}
		return result;
	}
	
	/* (non-Javadoc)
	 * @see Metier.I_Planning#saveUS(java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	public String saveUS(String id, String titre, String andon, String desc) throws SQLException{
		String result = "erreur Save";
		if(andon.equals("true")){
			andon="1";
		}else{
			andon="0";
		}
		int rs = this.bdd.update("UPDATE `userstory` SET `name`='"+titre+"', `planning_andon`='"+andon+"', `description`='"+desc+"' WHERE `id`= "+id);
		if(rs==1)
			result ="Save ok";
		return result;
	}
	
	/* (non-Javadoc)
	 * @see Metier.I_Planning#loadingAllUS()
	 */
	@Override
	public String loadingUS(String etat) throws SQLException{
		ResultSet rs = this.bdd.select("Select * FROM `userstory` WHERE `planning_state`='"+etat+"' ORDER BY `userstory`.`planning_order`");
		StringBuilder sb = new StringBuilder();
		String andon;
		while(rs.next()){
			if(rs.getBoolean("planning_andon")){
				andon ="help.gif";
			}else{
				andon = "no_help.png";
			}
			sb.append("<div id=\""+rs.getString("id")+"\" class=\"user-story\">"
					+ "<div class=\"user-story-titre\">"
					+ rs.getString("name")
					+ "</div>"
					+ "<div class=\"user-story-desc\">"
					+ rs.getString("description").replaceAll("\n","<BR />")
					+ "</div>"
					+ "<div class=\"user-story-andon\">"
					+ "<img class=\"img-andon\" id=\"img-andon"+rs.getString("id")+"\" src=\"images/"+andon+"\" />"
					+ "</div>"
					+ "<button class=\"toolbox\">ToolBox</button>"
					+ "</div>");
		}
		return sb.toString();
	}
	
	public String convertEtat(String etat){
		String result= "error convertEtat";
		if(etat.equals("c1")){
			result = "To do";
		}else{
			if(etat.equals("c2")){
				result = "In progress"; 
			}else{
				if(etat.equals("c3")){
					result = "Done";
				}
			}
			
		}
		return result;
	}
	
	/* (non-Javadoc)
	 * @see Metier.I_Planning#close()
	 */
	@Override
	public void close() throws SQLException{
		this.bdd.closeSt();
		this.bdd.closeBdd();
	}

}
